from datetime import timedelta, datetime

from app.utils.uuid import generate_app_fk
from app.utils.time import get_now_date
from app.database.postgre import SessionDep, col, select, text
from app.database.models import Account, App, CardType
from app.models import responses
from app.models.common import AppIdJSON, AppIdParams
from app.models.apps import (MyAppListParams, CreateAppJSON, UpdateAppJSON, BatchDeleteAppsJSON,
                             GetLoginTimesParams)


async def get_apps_list(account: Account, query_params: MyAppListParams, session: SessionDep):
    # 构建查询参数
    page = query_params.page
    page_size = query_params.page_size
    name = query_params.name

    # 构建查询语句
    query = session.query(App).filter(App.account_id == account.aid).order_by(col(App.id).asc())

    # 动态添加查询条件
    if name:
        query = query.filter(col(App.name).like(f"%{name}%"))

    # 获取总数
    total_count = query.count()
    total_pages = (total_count + page_size - 1) // page_size

    # 获取分页数据
    offset = (page - 1) * page_size
    paginated_query = query.offset(offset).limit(page_size)
    paginated_apps = paginated_query.all()

    return responses.SuccessResponse(
        data={
            'total': total_count,
            'total_pages': total_pages,
            'page': page,
            'page_size': len(paginated_apps),
            'list': [app.model_dump(exclude={'fk_version'}) for app in paginated_apps]
        }
    )


async def create_new_app(account: Account, json_data: CreateAppJSON, session: SessionDep):
    # 构建创建应用的参数
    app_data = json_data.model_dump(exclude_none=True)
    app_data['force_disabled'] = False
    app_data['account_id'] = account.aid
    app_data.update(dict(generate_app_fk(account.aid)))

    # 准备提交新应用
    new_app = App(**app_data)

    try:
        session.add(new_app)
        session.commit()
    except Exception as e:
        session.rollback()
        raise e

    session.refresh(new_app)

    # 创建初始卡种
    init_card_types = [CardType(**init_card_type) for init_card_type in CardType.init_card_types(new_app.id)]
    try:
        session.add_all(init_card_types)
        session.commit()
    except Exception as e:
        session.rollback()
        raise e

    session.refresh(new_app)

    return responses.SuccessResponse(msg='应用创建成功', data=new_app.model_dump(exclude={'fk_version'}))


async def get_app_info(account: Account, query_params: AppIdParams, session: SessionDep):
    # 获取被查询的应用
    app_id = query_params.app_id
    operate_app = session.exec(select(App).where(App.id == app_id, App.account_id == account.aid)).first()
    if not operate_app:
        return responses.Response303()

    return responses.SuccessResponse(data=operate_app.model_dump(exclude={'fk_version'}))


async def update_app_by_id(account: Account, json_data: UpdateAppJSON, session: SessionDep):
    # 构建更新应用的参数
    app_id = json_data.app_id
    other_data = json_data.model_dump(exclude={'app_id'}, exclude_none=True)

    # 获取被更新的应用
    app = session.exec(select(App).where(App.id == app_id, App.account_id == account.aid)).first()
    if not app:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    App.sqlmodel_update(app, other_data)
    session.commit()
    session.refresh(app)

    return responses.SuccessResponse(msg='应用更新成功', data=app.model_dump(exclude={'fk_version'}))


async def delete_app_by_id(account: Account, json_data: AppIdJSON, session: SessionDep):
    # 获取被删除的应用
    app_id = json_data.app_id
    app = session.exec(select(App).where(App.id == app_id, App.account_id == account.aid)).first()
    if not app:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    session.delete(app)
    session.commit()
    return responses.SuccessResponse(msg='应用已删除', data=app.model_dump(include={'id'}))


async def delete_app_by_ids(account: Account, json_data: BatchDeleteAppsJSON, session: SessionDep):
    """批量删除选定的软件"""
    # 检查应用是否被超管禁用
    apps = session.query(App).filter(
        App.account_id == account.aid,
        col(App.force_disabled).is_(False),
        col(App.id).in_(json_data.app_ids)
    ).all()
    if not apps:
        return responses.SuccessResponse(msg='没有找到指定的应用', data={'count': 0, 'ids': []})

    deleted_ids = [app.id for app in apps]

    session.query(App).filter(
        App.account_id == account.aid,
        col(App.id).in_(deleted_ids)
    ).delete(synchronize_session=False)

    session.commit()

    return responses.SuccessResponse(msg='选中的应用已删除', data={
        'count': len(deleted_ids),
        'ids': deleted_ids
    })


async def create_app_flag_key(account: Account, json_data: AppIdJSON, session: SessionDep):
    # 获取需要生成 fk 的应用
    app_id = json_data.app_id
    app = session.exec(select(App).where(App.id == app_id, App.account_id == account.aid)).first()
    if not app:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 更新 flag 与 key
    app_fk = generate_app_fk(account.aid)
    app.fk_version = app_fk.version
    app.flag = app_fk.flag
    app.key = app_fk.key
    session.commit()
    session.refresh(app)

    return responses.SuccessResponse(data=app.model_dump(include={'id', 'key', 'flag'}))


async def get_app_login_times(account: Account, json_data: GetLoginTimesParams, session: SessionDep):
    """获取应用的卡密登录时间统计"""
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    count_type = json_data.type
    now = get_now_date()

    if count_type in (3, 4, 5):
        month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        next_month = (month_start + timedelta(days=32)).replace(day=1)
        start, end = month_start, next_month
    else:
        start = now - _get_time_delta(count_type)
        end = now

    if count_type in (0, 1, 2, 3):
        stats_subquery = """
                SELECT 
                    date_trunc(:trunc_unit, create_time) AS time_block,
                    COUNT(*) AS count
                FROM tb_app_login_times
                WHERE app_id = :app_id
                GROUP BY time_block
            """
    elif count_type == 4:
        stats_subquery = """
                SELECT 
                    (EXTRACT(hour FROM create_time)::int / 4) AS time_block,
                    COUNT(*) AS count
                FROM tb_app_login_times
                WHERE 
                    app_id = :app_id
                    AND create_time >= :start 
                    AND create_time < :end
                GROUP BY time_block
            """
    elif count_type == 5:
        stats_subquery = """
                SELECT 
                    CASE
                        WHEN EXTRACT(hour FROM create_time) BETWEEN 0 AND 5 THEN 0
                        WHEN EXTRACT(hour FROM create_time) BETWEEN 6 AND 11 THEN 1
                        WHEN EXTRACT(hour FROM create_time) BETWEEN 12 AND 17 THEN 2
                        WHEN EXTRACT(hour FROM create_time) BETWEEN 18 AND 23 THEN 3
                    END AS time_block,
                    COUNT(*) AS count
                FROM tb_app_login_times
                WHERE 
                    app_id = :app_id
                    AND create_time >= :start 
                    AND create_time < :end
                GROUP BY time_block
            """
    else:
        raise responses.FailureResponseError(-102, msg='统计的类型不在范围内')

    time_series_sql = _build_time_series_sql(count_type, now)

    final_query = text(f"""
            SELECT COALESCE(stats.count, 0) AS count
            FROM ({time_series_sql}) AS time_slots
            LEFT JOIN (
                {stats_subquery}
            ) stats ON time_slots.slot = stats.time_block
            ORDER BY time_slots.slot
        """)

    params = {
        "app_id": app_id,
        "start": start,
        "end": end,
    }
    if count_type in (0, 1, 2, 3):
        params["trunc_unit"] = 'hour' if count_type == 0 else 'day'

    result = session.execute(final_query, params)
    data = [row.count for row in result.all()]

    return responses.SuccessResponse(data={'values': data})


def _build_time_series_sql(count_type: int, now: datetime) -> str:
    """动态生成时间序列 SQL"""
    if count_type == 0:  # 当日（生成24小时槽）
        return """
            SELECT generate_series(
                date_trunc('day', :start)::timestamp,
                date_trunc('day', :start)::timestamp + interval '23 hours',
                interval '1 hour'
            ) AS slot
            """
    elif count_type == 1:  # 三天内（生成3天槽）
        return """
            SELECT generate_series(
                date_trunc('day', :start)::timestamp,
                date_trunc('day', :end)::timestamp,
                interval '1 day'
            ) AS slot
            """
    elif count_type == 2:  # 本周（生成7天槽）
        week_start = now - timedelta(days=now.weekday())
        return f"""
            SELECT generate_series(
                '{week_start.strftime('%Y-%m-%d')}'::timestamp,
                '{week_start.strftime('%Y-%m-%d')}'::timestamp + interval '6 days',
                interval '1 day'
            ) AS slot
            """
    elif count_type == 3:  # 本月（动态生成当月天数槽）
        month_start = now.replace(day=1)
        next_month = (month_start + timedelta(days=32)).replace(day=1)
        return f"""
            SELECT generate_series(
                '{month_start.strftime('%Y-%m-%d')}'::timestamp,
                '{next_month.strftime('%Y-%m-%d')}'::timestamp - interval '1 day',
                interval '1 day'
            ) AS slot
            """
    elif count_type == 4:  # 生成 0-5 槽位 每天 6 个时段
        return "SELECT generate_series(0,5) AS slot"
    elif count_type == 5:  # 生成 0-3 的槽位（凌晨0-5/上午6-11/下午12-17/晚上18-23）
        return "SELECT generate_series(0,3) AS slot"


def _get_trunc_unit(count_type: int) -> str:
    """获取时间分组单位"""
    return 'hour' if count_type == 0 else 'day'


def _get_time_delta(count_type: int) -> timedelta:
    """获取时间范围"""
    return {
        0: timedelta(hours=0),
        1: timedelta(days=2),
        2: timedelta(weeks=1),
        3: timedelta(days=32),
        4: timedelta(days=32),
        5: timedelta(days=32)
    }[count_type]
